package Dao;

import entity.EmployeeBean;
import entity.PositiontypeBean;
import util.JDBCUtil;
import util.JdbcUtil2;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
public class EmployeeDao {
    /**
     * 获取所有员工所有信息
     * */
    public List<EmployeeBean> getEmployeeAll(){
        String sql = "select employee_id,Employees.positionType_id,employees_name,contact_info,employeesWages,employeeStatus,positionType_name from Employees,Positiontype where Positiontype.positionType_id = Employees.positionType_id";
        return JDBCUtil.list(sql,EmployeeBean.class);
    }
    /**
     * 通过id获取指定员工信息
     * */
    public EmployeeBean getEmployeeID(int employee_id){
        String sql = "select employee_id,positionType_id,employees_name,contact_info,employeesWages,employeeStatus,positionType_name from Employees,Positiontype where Positiontype.positionType_id = Employees.positionType_id and employee_id=?";
        return JDBCUtil.one(sql,EmployeeBean.class,employee_id);
    }
    /**
     * 添加员工
     * */
    public int addEmployee(EmployeeBean employeeBean){
        String sql = "insert into Employees(employees_name,positionType_id,contact_info,employeesWages,employeeStatus) values (?,?,?,?,?)";
        return JDBCUtil.update(sql,employeeBean.getEmployees_name(),employeeBean.getPositionType_id(),employeeBean.getContact_info(),employeeBean.getEmployeesWages(),employeeBean.getEmployeeStatus());
    }
    /**
     * 修改员工
     * */
    public int updateEmployeeById(EmployeeBean employeeBean) throws SQLException {
        Connection conn = JdbcUtil2.createConn();
        String sql = "update Employees set employees_name=?,positionType_id = (select positionType_id from Positiontype where positionType_name=?),contact_info=? where employee_id=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, employeeBean.getEmployees_name());
        pstmt.setString(2, employeeBean.getPositionType_name());
        pstmt.setString(3, employeeBean.getContact_info());
        pstmt.setInt(4, employeeBean.getEmployee_id());
        return pstmt.executeUpdate();
    }
    /**
     * 修改员工工资
     * */
    public int updateEmployeesWages(BigDecimal employeesWages, int employee_id){
        String sql = "update Employees set employeesWages=? where employee_id=?";
        return JDBCUtil.update(sql,employeesWages,employee_id);
    }

    /**
     * 通过类型名查询id
     * */
    public PositiontypeBean getByPositionType_name(String PositionType_name){
        String sql = "select positionType_id,positionType_name,basicSalary from Positiontype where positionType_name=?";
        return JDBCUtil.one(sql, PositiontypeBean.class,PositionType_name);
    }
    /**查询员工名称**/
    public List<EmployeeBean> getEmployeeName(String employee_name){
        String sql = "select employee_id,Employees.positionType_id,employees_name,contact_info,employeesWages,employeeStatus,positionType_name from Employees,Positiontype where Positiontype.positionType_id = Employees.positionType_id and employees_name like '%"+employee_name+"%'";
        return JDBCUtil.list(sql, EmployeeBean.class);
    }
    /**
     * 查询在职员工
     * */
    public List<EmployeeBean> getEmployeeStateAll(boolean employeeStatus){
        String sql = "select employee_id,positionType_id,employees_name,contact_info,employeesWages,employeeStatus,positionType_name from Employees,Positiontype where Positiontype.positionType_id = Employees.positionType_id and employeeStatus=?";
        return JDBCUtil.list(sql,EmployeeBean.class,employeeStatus);
    }
}